using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using PHSRAG.Insight21.BusinessObjects;
using PHSRAG.Insight21.Policy;
using PHSRAG.Insight21.SearchContext;
using PHSRAG.Insight21.InsightUtilities;

namespace PHSRAG.Insight21.ClinicalTrials
{
   public class BudgetSummaries : SaveableBusinessObject
   {

      #region Constants
      private const string BudgetSummariesKey = "budgetSummaries_EEDDA3F0-22BE-4571-9D4D-83F7DD1EF403";
      private const string FilteredDataTableName = "filtered";
      private const string OriginalDataTableName = "original";
      #endregion

      #region Instance Variables
      private Hashtable budgetSummaryMap = new Hashtable( );
      private string currentDataTableName = OriginalDataTableName;
      private bool sortAscending;
      private string sortColumn = "LastModifiedDate";
      private SqlCommand deleteBudgetCommand;
      #endregion

      #region Constructor
      /// <summary>
      /// Initialize the base class with the specified cache policy and create the data container.
      /// </summary>
      public BudgetSummaries(ICachePolicy cachePolicy) : base(cachePolicy)
      {
      }
      #endregion

      #region Static Methods
      /// <summary>
      /// Returns the cache key for the BudgetSummaries class
      /// </summary>
      public static string CacheKey
      {
         get
         {
            return BudgetSummariesKey;
         }
      }
      #endregion


      #region Public Properties
      /// <summary>
      /// Returns the number of BudgetSummary rows.  Used for paging
      /// </summary>
      public int Count
      {
         get
         {
            return (DataContainer.Tables.Count == 0) ? 0 : DataContainer.Tables[0].Rows.Count;
         }
      }

      /// <summary>
      /// Returns a DataTable representing the Budget Summaries for use by Crystal
      /// </summary>
      public DataTable ReportData
      {
         get
         {
            return DataContainer.Tables[currentDataTableName];
         }
      }	  

      /// <summary>
      /// Boolean indicating whether to sort ascending or not
      /// </summary>
      public bool SortAscending
      {
         get
         {
            return sortAscending;
         }
      }

      /// <summary>
      /// Sort Column
      /// </summary>
      public string SortColumn
      {
         get
         {
            return sortColumn;
         }
      }
      #endregion

      #region Base Methods
      /// <summary>
      /// Cache this instance using the prevalent cache policy.
      /// </summary>
      public override void Cache( )
      {
         CachePolicy.Cache( BudgetSummariesKey, this );
      }

      /// <summary>
      /// Remove this instance from the cache using the prevalent cache policy.
      /// </summary>
      public override void UnCache( )
      {
         CachePolicy.UnCache( BudgetSummariesKey );
      }

      /// <summary>
      /// Load budget summaries.
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Object array which must contain a domain user Id.
      /// The id is not currently used.</param>
      public override void Load( string connectionString, params object[] args )
      {
         if ( args[0] == null )
            throw new Exception("BudgetSummaries load requires a domain user ID");

         SearchCriteria searchCriteria = (SearchCriteria) args[0];

         if( DataContainer != null )
            DataContainer.Reset( );

         DataContainer =	Utility.DataAccess.GetDataSet(connectionString, "GetClinicalTrialBudgetSummaries",
            Utility.DataAccess.BuildSqlParameterArray(
            "@domainUserID", SqlDbType.Int, searchCriteria["DomainUserId"],
            "@PIId", SqlDbType.Int, searchCriteria[Constants.PIIDKey],
            "@trialName", SqlDbType.VarChar, searchCriteria["trialName"],
            "@sponsorName", SqlDbType.VarChar, searchCriteria["sponsorName"],
            "@originatorName", SqlDbType.VarChar, searchCriteria["originatorName"],
            "@folderNumber", SqlDbType.VarChar, searchCriteria["folderNumber"],
            "@status", SqlDbType.VarChar, searchCriteria["status"]
            ));

         if ( DataContainer.Tables.Count == 0 )
            throw new Exception("Failed to load Budget Summary information.");

         DataContainer.Tables[0].TableName = OriginalDataTableName;

         DataView dataView = new DataView(DataContainer.Tables[OriginalDataTableName]);
         dataView.Sort = String.Format("{0} {1}",SortColumn, SortAscending ? Constants.SortViewAscending: Constants.SortViewDescending);

         DataTable sortedDataTable = DataContainer.Tables[OriginalDataTableName].Clone();
         foreach (DataRowView rowView in dataView)
            sortedDataTable.ImportRow(rowView.Row);

         DataContainer.Tables.Remove( OriginalDataTableName );
         DataContainer.Tables.Add( sortedDataTable );
      }

      /// <summary>
      /// Saves budget summaries.  Only used to delete selected budgets.
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Optional parameters (ignored)</param>
      public override void Save( string connectionString, params object[] args )
      {
         SqlConnection connection = new SqlConnection( connectionString );
         SqlTransaction transaction = null;
         try
         {
            connection.Open( );
            transaction = connection.BeginTransaction( );
            BuildCommand( );
            deleteBudgetCommand.Connection = connection;
            deleteBudgetCommand.Transaction = transaction;

            SqlDataAdapter adapter = new SqlDataAdapter( );
            adapter.DeleteCommand = deleteBudgetCommand;
            adapter.Update( DataContainer, OriginalDataTableName );

            transaction.Commit( );
         }
         catch( Exception e )
         {
            transaction.Rollback( );
            throw(new Exception(@"Error deleting Budgets from the database.", e));
         }
         finally
         {
            if ( connection.State != ConnectionState.Closed )
               connection.Close( );
            transaction.Dispose( );
         }
      }
      #endregion

      #region Private Methods
      private void BuildCommand( )
      {
         if ( deleteBudgetCommand == null )
         {
            deleteBudgetCommand = new SqlCommand( );
            deleteBudgetCommand.CommandText = "DeleteClinicalTrialBudget";
            deleteBudgetCommand.CommandType = CommandType.StoredProcedure;
			deleteBudgetCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			((SqlParameter)deleteBudgetCommand.Parameters.Add( "@budgetId", SqlDbType.Int )).SourceColumn = "Id";
         }
      }
      #endregion

      #region Public Methods
      /// <summary>
      /// Returns the budget summaries as an ArrayList.  Used by datagrid
      /// </summary>
      /// <param name="startIndex">First item to return</param>
      /// <param name="count">How many items to return</param>
      /// <returns>ArrayList</returns>
      public ArrayList GetBudgetList( int startIndex, int count )
      {
         ArrayList budgetList = budgetSummaryMap[startIndex] as ArrayList;
         if ( budgetList == null )
         {
            budgetList = new ArrayList( );
            DataTable dataTable = DataContainer.Tables[currentDataTableName];
            for ( int i = startIndex, end = Math.Min(i + count, dataTable.Rows.Count); i < end; ++i )
               budgetList.Add( new BudgetSummary( CachePolicy, dataTable.Rows[i] ) );
            budgetSummaryMap[startIndex] = budgetList;
         }
         return budgetList;
      }

      /// <summary>
      /// Search.  Loads budget summaries with specified search criteria.
      /// </summary>
      /// <param name="searchCriteria"></param>
      /// <returns>bool</returns>
      public bool Search( SearchCriteria searchCriteria )
      {
         budgetSummaryMap.Clear( );
         Load( Utility.DataAccess.ConnectionString, searchCriteria );

         return true;
      }

      /// <summary>
      /// Sort.  Orders budget summaries by column specified.
      /// </summary>
      /// <param name="columnName"></param>
      public void Sort( string columnName )
      {
         budgetSummaryMap.Clear( );

         sortAscending = (sortColumn == columnName) ? !sortAscending : true;
         sortColumn = columnName;

         DataTable dataTable = DataContainer.Tables[currentDataTableName];
         DataView dataView = dataTable.DefaultView;
         dataView.Sort = string.Format("{0} {1}", columnName, sortAscending ? "ASC" : "DESC");

         DataTable sortedDataTable = dataTable.Clone( );
         foreach (DataRowView rowView in dataView)
            sortedDataTable.ImportRow(rowView.Row);

         DataContainer.Tables.Remove(currentDataTableName);
         DataContainer.Tables.Add(sortedDataTable);
      }
      #endregion
   }// END CLASS DEFINITION BudgetSummaries
}